package avicit.bdp.dcs.semistructure.service;

import avicit.bdp.common.base.BaseService;
import avicit.bdp.common.utils.BdpLogUtil;
import avicit.bdp.common.utils.enums.FileType;
import avicit.bdp.core.constant.Constants;
import avicit.bdp.core.poi.enums.ExcelTypeEnum;
import avicit.bdp.core.poi.util.xml.XmlUtils;
import avicit.bdp.core.util.json.JSONUtils;
import avicit.bdp.dcs.semistructure.dao.FileTemplateDao;
import avicit.bdp.dcs.semistructure.dto.FileTemplateDTO;
import avicit.bdp.dcs.semistructure.dto.TemplateFieldDTO;
import avicit.bdp.dcs.utils.MsgTemplateExcelUtils;
import avicit.platform6.api.system.ConvertColumnClient;
import avicit.platform6.api.system.impl.SystemConstant;
import avicit.platform6.commons.utils.BusinessUtil;
import avicit.platform6.commons.utils.ComUtil;
import avicit.platform6.core.context.ThreadContextHelper;
import avicit.platform6.core.exception.BusinessException;
import avicit.platform6.core.properties.PlatformConstant;
import avicit.platform6.core.rest.msg.QueryRespBean;
import com.github.pagehelper.Page;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @金航数码科技有限责任公司
 * @作者：liyb
 * @邮箱：liyb@avic-digital.com
 * @创建时间：2023-04-03 14:10
 * @类说明：文件模板管理
 * @修改记录：
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class FileTemplateService extends BaseService<FileTemplateDao, FileTemplateDTO> {

  private static final Logger logger = LoggerFactory.getLogger(FileTemplateService.class);

  /** 排序，按修改时间倒序：1，按修改时间升序：2，按名称升序：3，按名称倒序：4 */
  private static final int TIME_DESC = 1;

  private static final int TIME_ASC = 2;
  private static final int NAME_ASC = 3;
  private static final int NAME_DESC = 4;

  /** 排序字段 */
  private static final String ORDER_FIELD_LAST_UPDATE_DATE = "last_update_date";

  private static final String ORDER_FIELD_TEMPLATE_NAME = "name";

  /** 排序类型 */
  private static final String ORDER_TYPE_DESC = "desc";

  private static final String ORDER_TYPE_ASC = "asc";

  @Value("${file.type.template}")
  private String templateType;

  @Value("${file.templateColumnRule}")
  private String templateColumnRule;

  private static final String TRANSFER_VERTICAL = "\\|";
  private static final String TEMPLATE_SEPARATOR_PATTERN = "^[t, ;|_-]${1}";
  private static final String STRING = "STRING";

  @Autowired private FileTemplateDao templateDao;
  @Autowired private TemplateFieldService fieldService;
  @Autowired
  private ConvertColumnClient convertColumnClient;

  /**
   * 查询模板列表
   *
   * @param sort
   * @param name
   * @param pageNo
   * @param pageSize
   * @return
   */
  public QueryRespBean<FileTemplateDTO> getTemplateList(Integer sort, String name, String projectId, Integer status, Integer pageNo, Integer pageSize) {
    QueryRespBean<FileTemplateDTO> queryRespBean = new QueryRespBean<>();
    com.github.pagehelper.PageHelper.startPage(pageNo, pageSize);
    String orderField = ORDER_FIELD_LAST_UPDATE_DATE;
    String orderType = ORDER_TYPE_DESC;
    if (sort != null) {
      if (TIME_DESC == sort) {
        orderField = ORDER_FIELD_LAST_UPDATE_DATE;
        orderType = ORDER_TYPE_DESC;
      } else if (TIME_ASC == sort) {
        orderField = ORDER_FIELD_LAST_UPDATE_DATE;
        orderType = ORDER_TYPE_ASC;
      } else if (NAME_ASC == sort) {
        orderField = ORDER_FIELD_TEMPLATE_NAME;
        orderType = ORDER_TYPE_ASC;
      } else if (NAME_DESC == sort) {
        orderField = ORDER_FIELD_TEMPLATE_NAME;
        orderType = ORDER_TYPE_DESC;
      }
    }

    Page<FileTemplateDTO> templateList = templateDao.getTemplateList(name, orderField, orderType, projectId, status);

    //循环组装请求数据
    Map<String, Set<String>> convertFormData = new HashMap<>();
    for (FileTemplateDTO template : templateList) {
      BusinessUtil.createConvertSet(convertFormData, SystemConstant.USER, template.getCreatedBy());
      BusinessUtil.createConvertSet(convertFormData, Constants.BDP_DATA_SECRET_LEVEL_KEY, template.getSecretLevel());
    }
    if (convertFormData.size() > 0) {
      //获取请求结果
      Map<String, Map<String, String>> convertResultData = convertColumnClient.replace(convertFormData);
      //循环设置Alias或Name的值
      for (FileTemplateDTO template : templateList) {
        template.setCreatedBy(BusinessUtil.convertFormat(convertResultData, SystemConstant.USER, template.getCreatedBy()));
        template.setSecretLevelName(BusinessUtil.convertFormat(
                convertResultData, Constants.BDP_DATA_SECRET_LEVEL_KEY, template.getSecretLevel()));
      }
    }

    if (CollectionUtils.isNotEmpty(templateList)) {
      for (FileTemplateDTO template : templateList) {
        if (template.getType() == FileType.CSV.getCode()) {
          template.setTypeString(FileType.CSV.getDescp());
        } else if (template.getType() == FileType.EXCEL2007.getCode()) {
          template.setTypeString(FileType.EXCEL2007.getDescp());
        } else if (template.getType() == FileType.JSON.getCode()) {
          template.setTypeString(FileType.JSON.getDescp());
        }
      }
    }
    queryRespBean.setResult(templateList);
    BdpLogUtil.log4Query(queryRespBean);
    return queryRespBean;
  }

  /**
   * 新增模板
   *
   * @param templateDTO
   * @return
   * @throws Exception
   */
  public String createTemplate(FileTemplateDTO templateDTO) throws Exception {
    if (StringUtils.isEmpty(templateDTO.getName())) {
      throw new BusinessException("模板名称不能为空");
    }
    if (StringUtils.isEmpty(templateDTO.getCode())) {
      throw new BusinessException("模板代码不能为空");
    }
    if (StringUtils.isEmpty(templateDTO.getTemplateField())) {
      throw new BusinessException("字段不能为空");
    }

    // 模板代码唯一性校验
    if (!checkCodeUnique(templateDTO.getCode(), templateDTO.getProjectId())) {
      throw new BusinessException("模板代码已经存在");
    }
    String templateId = ComUtil.getId();
    templateDTO.setId(templateId);

    // 记录日志
    BdpLogUtil.log4Insert(templateDTO);
    this.insert(templateDTO);

    // 批量新增模板字段
    List<TemplateFieldDTO> templateFieldList = JSONUtils.toList(templateDTO.getTemplateField(), TemplateFieldDTO.class);
    if (CollectionUtils.isEmpty(templateFieldList)) {
      throw new BusinessException("字段不能为空");
    }
    fieldService.batchInsertTemplateField(templateFieldList, templateId);
    return templateId;
  }

  /**
   * 模板代码唯一性校验
   *
   * @param code
   * @return
   */
  private boolean checkCodeUnique(String code, String projectId) {
    FileTemplateDTO templateDTO = new FileTemplateDTO();
    templateDTO.setCode(code);
    templateDTO.setProjectId(projectId);
    long num = selectCount(templateDTO);

    if (num > 0) {
      return false;
    } else {
      return true;
    }
  }

  /**
   * 修改模板
   *
   * @param templateDTO
   * @return
   * @throws Exception
   */
  public void updateTemplate(FileTemplateDTO templateDTO) throws Exception {
    if (StringUtils.isEmpty(templateDTO.getId())) {
      throw new BusinessException("id不能为空");
    }
    if (StringUtils.isEmpty(templateDTO.getName())) {
      throw new BusinessException("模板名称不能为空");
    }
    if (StringUtils.isEmpty(templateDTO.getCode())) {
      throw new BusinessException("模板代码不能为空");
    }
    if (StringUtils.isEmpty(templateDTO.getTemplateField())) {
      throw new BusinessException("字段不能为空");
    }

    FileTemplateDTO oldTemplate = this.selectByPrimaryKey(templateDTO.getId());
    if (oldTemplate == null) {
      throw new BusinessException(String.format("id=%s的模板不存在", templateDTO.getId()));
    }

    // 模板代码唯一性校验
    if (!templateDTO.getCode().equals(oldTemplate.getCode())) {
      if (!checkCodeUnique(templateDTO.getCode(), templateDTO.getProjectId())) {
        throw new BusinessException("模板代码已经存在");
      }
    }

    // 记录日志
    BdpLogUtil.log4Update(templateDTO, oldTemplate);
    this.updateByPrimaryKeySelective(templateDTO);

    // 批量修改模板字段
    List<TemplateFieldDTO> templateFieldList =
        JSONUtils.toList(templateDTO.getTemplateField(), TemplateFieldDTO.class);
    if (CollectionUtils.isEmpty(templateFieldList)) {
      throw new BusinessException("字段不能为空");
    }
    fieldService.batchUpdateTemplateField(templateFieldList, templateDTO.getId());
  }

  /**
   * 批量删除模板
   *
   * @param templateDTO
   * @throws Exception
   */
  public void deleteTemplateByIds(FileTemplateDTO templateDTO) throws Exception {
    if (StringUtils.isEmpty(templateDTO.getId())) {
      throw new BusinessException("待删除模板id不能为空");
    }

    String idList = templateDTO.getId();
    logger.info("待删除模板idList:{}", idList);

    String[] ids = idList.split(Constants.COMMA);
    for (String templateId : ids) {
      // 判断模板是否关联目录，关联目录不能删除

      // 删除对应模板
      FileTemplateDTO templateDTOQuery = this.selectByPrimaryKey(templateId);
      if (templateDTOQuery == null) {
        throw new BusinessException(String.format("id=%s的模板不存在", templateId));
      }

      // 记录日志
      BdpLogUtil.log4Delete(templateDTOQuery);

      // 删除数据库模板记录
      this.deleteByPrimaryKey(templateId);

      // 删除模板对应字段
      fieldService.deleteByTemplateId(templateId);
    }
  }

  /**
   * 查看模板
   *
   * @param templateId
   * @return
   * @throws Exception
   */
  public FileTemplateDTO getTemplateById(String templateId) throws Exception {
    if (StringUtils.isEmpty(templateId)) {
      throw new BusinessException("id不能为空");
    }

    FileTemplateDTO templateDTO = this.selectByPrimaryKey(templateId);
    if (templateDTO == null) {
      throw new BusinessException(String.format("id=%s的模板不存在", templateId));
    }

    TemplateFieldDTO templateField = new TemplateFieldDTO();
    templateField.setTemplateId(templateId);
    List<TemplateFieldDTO> templateFieldList = fieldService.selectList(templateField);
    templateDTO.setTemplateFieldList(templateFieldList);

    return templateDTO;
  }

  /**
   * 启用/停用
   *
   * @return
   * @throws Exception
   */
  public boolean updateStatus(String templateId, Integer status) throws Exception {
    if (StringUtils.isEmpty(templateId)) {
      throw new BusinessException("id不能为空");
    }

    FileTemplateDTO templateDTOQuery = this.selectByPrimaryKey(templateId);
    if (templateDTOQuery == null) {
      throw new BusinessException(String.format("id=%s的模板不存在", templateId));
    }

    templateDTOQuery.setStatus(status);

    // 记录日志
    String logTitle = "启用/停用模板：【" + templateDTOQuery.getName() + "】";
    BdpLogUtil.log("文件模板管理模块", logTitle, PlatformConstant.OpType.update);

    this.updateByPrimaryKeySelective(templateDTOQuery);
    return true;
  }

  /**
   * 模板文件上传
   *
   * @param templateDTO
   * @throws Exception
   */
  public List<TemplateFieldDTO> upload(FileTemplateDTO templateDTO) throws Exception {
    // 检查是否标准模板
    if (checkStandardTemplate(templateDTO)) {
      // 直接解析标准模板中数据
      return MsgTemplateExcelUtils.parseStandardTemplate(templateDTO.getFile().getInputStream());
    } else {
      // 根据指定列名行、分隔符、文件类型分别解析数据
      return parseCommonTemplate(templateDTO);
    }
  }

  /**
   * 解析普通模板中数据
   *
   * @param templateDTO
   */
  public List<TemplateFieldDTO> parseCommonTemplate(FileTemplateDTO templateDTO) throws Exception {
    // 1、参数校验
    checkParams(templateDTO);

    InputStream inputStream = templateDTO.getFile().getInputStream();
    int startLine = Integer.parseInt(templateDTO.getStartLine());
    String suffix = getSuffix(templateDTO.getFile());
    String line = "";

    if (FileType.EXCEL2003.getSuffix().equalsIgnoreCase(suffix)
        || FileType.EXCEL2007.getSuffix().equalsIgnoreCase(suffix)) {
      line = MsgTemplateExcelUtils.getHeaderByStartLine(inputStream, suffix, startLine);
    } else if (FileType.CSV.getSuffix().equalsIgnoreCase(suffix)
        || FileType.TEXT.getSuffix().equalsIgnoreCase(suffix)) {
      line = getCsvHeaderByStartLine(inputStream, startLine);
    }else {
      throw new BusinessException(String.format("模板仅支持excel、txt、csv格式"));
    }

    logger.info("模板信息为：{},分隔符为：{}", line, templateDTO.getFileSeparator());

    String[] fields;
    if (Constants.VERTICAL.equals(templateDTO.getFileSeparator())) {
      // |分隔有问题，需要转义一下
      fields = line.split(TRANSFER_VERTICAL);
    } else {
      fields = line.split(templateDTO.getFileSeparator());
    }

    // 对模板列字段进行校验
    List<TemplateFieldDTO> fieldList = new ArrayList<>();
    logger.info("模板字段信息为：{},字段数量为：{}", fields, fields.length);
    int i = 1;
    for (String field : fields) {
      field = field .trim();
      if(StringUtils.isEmpty(field)){
        throw new BusinessException(String.format("第%s列字段为空，请修改模板", i));
      }
      if (!isValid(templateColumnRule, field)) {
        throw new BusinessException(
            String.format("模板格式错误，模板列只允许数字、字母大小写、下划线、横杠，且第一位是字母，字段长度限制100。%s字段不满足", field));
      }

      TemplateFieldDTO fieldDTO = new TemplateFieldDTO();
      fieldDTO.setOrderBy(i);
      fieldDTO.setName(field);
      fieldDTO.setType(STRING);
      fieldList.add(fieldDTO);
      i++;
    }

    return fieldList;
  }

  private boolean isValid(String regex, String name) {
    boolean flag = true;
    if(name.equals("\\t")){
      name = name.replace("\\t", "t");
    }

    if(name.contains("\"")){
      name = name.replace("\"", "");
    }

    Pattern pattern = Pattern.compile(regex);
    Matcher matcher = pattern.matcher(name);
    if(!matcher.matches()){
      flag = false;
    }
    return flag;
  }

  private boolean checkParams(FileTemplateDTO templateDTO) {
    String separator = templateDTO.getFileSeparator();
    if (StringUtils.isEmpty(templateDTO.getStartLine())) {
      throw new BusinessException("指定列名行不能为空！");
    }

    String suffix = getSuffix(templateDTO.getFile());
    String[] types = templateType.split("\\|");
    List<String> typeList = Arrays.asList(types);
    if (!typeList.contains(suffix)) {
      throw new BusinessException("上传模板文件类型仅支持:" + templateType);
    }

    // 校验分隔符
    if (!isValid(TEMPLATE_SEPARATOR_PATTERN, separator)) {
      throw new BusinessException(String.format("上传模板文件分隔符请匹配%s正则表达式", TEMPLATE_SEPARATOR_PATTERN));
    }

    return true;
  }

  /**
   * 检查是否标准模板
   *
   * @param templateDTO
   * @return
   */
  private boolean checkStandardTemplate(FileTemplateDTO templateDTO) throws Exception {
    String suffix = getSuffix(templateDTO.getFile());
    if (!ExcelTypeEnum.XLSX.getValue().equalsIgnoreCase(suffix)) {
      return false;
    }

    return MsgTemplateExcelUtils.checkStandardTemplate(templateDTO.getFile().getInputStream());
  }

  /**
   * 获取文件后缀名
   *
   * @return
   */
  private String getSuffix(MultipartFile file) {
    String filename = file.getOriginalFilename();
    String[] split = filename.split("\\.");
    String suffix = split[split.length - 1];
    return suffix;
  }

  /**
   * 获取csv、txt表头
   *
   * @param is
   * @param startLine
   * @return
   */
  public static String getCsvHeaderByStartLine(InputStream is, int startLine) throws Exception {
    String header = "";
    // 解析模板文件返回列信息
    BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(is, "UTF-8"));

    for (int i = 0; i < startLine; i++) {
      header = bufferedReader.readLine();
    }

    if (bufferedReader != null) {
      try {
        bufferedReader.close();
      } catch (Exception e) {
        logger.error(e.getMessage(), e);
      }
    }
    return header;
  }

  private FileTemplateDTO getTemplateByCode(String code, String projectId) {
    FileTemplateDTO templateDTO = new FileTemplateDTO();
    templateDTO.setCode(code);
    templateDTO.setProjectId(projectId);
    return this.selectOne(templateDTO);
  }

  /**
   * 导入文件模板
   */
  public void importTemplate(FileTemplateDTO fileTemplate) {
    List<FileTemplateDTO> templateList = new ArrayList<>();
    MultipartFile file = fileTemplate.getFile();
    String projectId = fileTemplate.getProjectId();
    boolean overwrite = fileTemplate.isOverwrite();

    try {
      templateList = (List<FileTemplateDTO>) XmlUtils.fromXml(file.getInputStream(), templateList);

      if (CollectionUtils.isNotEmpty(templateList)) {
        for(FileTemplateDTO template : templateList){
          // 覆盖
          if(overwrite){
            cover(template, projectId);
          }else{
            noCover(template, projectId);
          }
        }
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
  }

  /**
   * 覆盖模板
   * @param template
   * @param projectId
   */
  private void cover(FileTemplateDTO template, String projectId){
    List<TemplateFieldDTO> templateFieldList = template.getTemplateFieldList();
    if (checkCodeUnique(template.getCode(), projectId)) {
      template.setId(ComUtil.getId());
      template.setProjectId(projectId);
      template.setStatus(0);
      this.insert(template);

      if (CollectionUtils.isNotEmpty(templateFieldList)) {
        fieldService.batchInsertTemplateField(templateFieldList, template.getId());
      }
    }else{
      FileTemplateDTO templateDTO = getTemplateByCode(template.getCode(), projectId);
      templateDTO.setProjectId(projectId);
      templateDTO.setName(template.getName());
      templateDTO.setCode(template.getCode());
      templateDTO.setType(template.getType());
      templateDTO.setStatus(0);
      templateDTO.setStartLine(template.getStartLine());
      templateDTO.setSheetTab(template.getSheetTab());
      templateDTO.setFileSeparator(template.getFileSeparator());
      templateDTO.setQuotation(template.getQuotation());
      templateDTO.setSingleQuotation(template.getSingleQuotation());
      templateDTO.setDoubleQuotation(template.getDoubleQuotation());
      templateDTO.setRemark(template.getRemark());
      this.updateByPrimaryKeySelective(templateDTO);

      if (CollectionUtils.isNotEmpty(templateFieldList)) {
        fieldService.batchUpdateTemplateField(templateFieldList, templateDTO.getId());
      }
    }
  }

  /**
   * 不覆盖模板
   * @param template
   * @param projectId
   */
  private void noCover(FileTemplateDTO template, String projectId){
    List<TemplateFieldDTO> templateFieldList = template.getTemplateFieldList();
    if (checkCodeUnique(template.getCode(), projectId)) {
      template.setId(ComUtil.getId());
      template.setProjectId(projectId);
      template.setStatus(0);
      this.insert(template);

      if (CollectionUtils.isNotEmpty(templateFieldList)) {
        fieldService.batchInsertTemplateField(templateFieldList, template.getId());
      }
    }else{
      // 查找模板编码，并重命名code
      String originCode = template.getCode();
      String code = template.getCode();
      FileTemplateDTO templateDTO = getTemplateByCode(code, projectId);
      int i = 1;
      while(templateDTO != null){
        code = originCode + "-" + i;
        templateDTO = getTemplateByCode(code, projectId);
        i++;
      }

      template.setId(ComUtil.getId());
      template.setProjectId(projectId);
      template.setStatus(0);
      template.setCode(code);
      this.insert(template);

      if (CollectionUtils.isNotEmpty(templateFieldList)) {
        fieldService.batchInsertTemplateField(templateFieldList, template.getId());
      }
    }
  }

  /**
   * 导出文件模板
   * @param id
   * @param response
   * @return
   */
  public void exportTemplate(String id, HttpServletResponse response) {
    if (StringUtils.isBlank(id)) {
      throw new BusinessException("模板定义ID为空.");
    }

    List<FileTemplateDTO> templateList = new ArrayList<>();
    String[] arr = id.split(Constants.COMMA);
    for(String templateId : arr){
      FileTemplateDTO template = this.selectByPrimaryKey(templateId);
      if (template == null) {
        throw new BusinessException(String.format("id=%s的模板不存在", templateId));
      }

      template.setTemplateFieldList(fieldService.getTemplateFieldListById(templateId));
      templateList.add(template);
    }

    exportTemplate(templateList, response);
  }

  private void exportTemplate(List<FileTemplateDTO> template, HttpServletResponse response) {
    try {
      /*************************************序列化为xml*************************************/
      ByteArrayOutputStream byteOutput = new ByteArrayOutputStream();
      OutputStream outputStream = response.getOutputStream();
      String name = URLEncoder.encode("模板数据", "utf-8");

      // 将Java对象序列化成XML
      PrintWriter writer = new PrintWriter(byteOutput);
      XmlUtils.toXml(template, writer);
      response.setContentType("application/xml; charset=utf-8");
      response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xml");
      response.setHeader("Pragma", "No-cache");
      response.setHeader("Expires", "0");
      byteOutput.writeTo(outputStream);
      byteOutput.flush();
      outputStream.flush();
      byteOutput.close();
      writer.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

}
